---------------------------------------------------
-- @name: stored_outlines_vs_sql_profile
-- @author: dion cho
-- @description: test for stored outline and sql profile
--------------------------------------------------

@capture_on

-- create objects
drop table t1 purge;

create table t1(c1 int, c2 int);

create index t1_n1 on t1(c1);
create index t1_n2 on t1(c2);

insert into t1
select level, level
from dual
connect by level <= 1000
;

@gather t1


--------------------------------------------------
-- case1: switching outline

-- this is "current" plan
explain plan for
select /*+ full(t1) */ 
  *
from t1
where c1 = 1 or c2 = 2
;

@plan

-- want to change to this
explain plan for
select /*+ use_concat */ 
  *
from t1
where c1 = 1 or c2 = 2
;

@plan

-- create "bad" outline
create or replace outline test_outln1
on
select /*+ full(t1) */ 
  *
from t1
where c1 = 1 or c2 = 2
;


-- create "good" outline
create or replace outline test_outln2
on
select /*+ use_concat */ 
  *
from t1
where c1 = 1 or c2 = 2
;

-- check outline
select hint
from user_outline_hints
where name = 'TEST_OUTLN2'
;


-- update outline
update outln.ol$
set hintcount = (
  select hintcount 
  from outln.ol$
  where ol_name = 'TEST_OUTLN2')
where
  ol_name = 'TEST_OUTLN1'
;

delete from outln.ol$hints
where ol_name = 'TEST_OUTLN1'
;

update outln.ol$hints 
set ol_name = 'TEST_OUTLN1'
where ol_name = 'TEST_OUTLN2'
;

delete from outln.ol$nodes
where ol_name = 'TEST_OUTLN1'
;

update outln.ol$nodes 
set ol_name = 'TEST_OUTLN1'
where ol_name = 'TEST_OUTLN2'
;

commit;

-- see that stored outline is used in explain plan
alter session set use_stored_outlines = true;

explain plan for
select /*+ full(t1) */ 
  *
from t1
where c1 = 1 or c2 = 2
;

@plan

drop outline test_outln1;
drop outline test_outln2;


--------------------------------------------------
-- case2: importing sql file

-- this is "bad" plan
explain plan for 
select /*+ full(t1) */ 
  *
from t1
where c1 = 1 or c2 = 2
;

@plan


-- this is "good" plan
explain plan for 
select /*+ use_concat */ 
  *
from t1
where c1 = 1 or c2 = 2
;

@plan

-- what is "full" hint naming convention?
select * from table(dbms_xplan.display(null, null, 'advanced'));

-- import sql profile manually
begin

  dbms_sqltune.import_sql_profile(
    name => 'test_prof',
    sql_text =>
'select /*+ full(t1) */ 
  *
from t1
where c1 = 1 or c2 = 2',
    profile => sqlprof_attr('USE_CONCAT(@"SEL$1" 8)',
                            'INDEX(@"SEL$1_2" "T1"@"SEL$1_2" ("T1"."C1"))',
                            'INDEX(@"SEL$1_1" "T1"@"SEL$1" ("T1"."C2"))')
  );
end;
/


explain plan for
select /*+ full(t1) */ 
  *
from t1
where c1 = 1 or c2 = 2
;

@plan


-- check hints applied
select
    a.attr#, a.attr_val
from
  sys.sqlprof$ p,
  sys.sqlprof$attr a
where
  p.signature = a.signature and
  p.sp_name = 'test_prof'
;


exec dbms_sqltune.drop_sql_profile('test_prof', true);

@capture_off
